using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;
namespace LdExpress
{
    public class GroupPermission
    {
        public long ID = 0;
        public string Code = "";
        public string Name = "";
        public string Description = "";
        public string Location = "";
        public long OrderNumber = 0;
        public GroupPermission()
        {
        }

        public GroupPermission(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_Groups";

            if (ID <= 0) // new object, we insert new record to database
            {
                strSQL = "INSERT INTO " + strTableName + "(" +
                "Code," +
                "Name," +
                "Description," +
                "Location," +
                "OrderNumber" +
                ")" +
                "VALUES(" +
                "'" + DBModule.RefineString(Code) + "'" +
                ",'" + DBModule.RefineString(Name) + "'" +
                ",'" + DBModule.RefineString(Description) + "'" +
                ",'" + DBModule.RefineString(Location) + "'" +
                "," + OrderNumber.ToString() +
                ");";

                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("GroupPermission", "add", cn, trans);
                //throw new Exception(strSQL);
                //GV.Response.Write(strSQL);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                             " Code='" + DBModule.RefineString(Code) + "'" +
                             ", Name='" + DBModule.RefineString(Name) + "'" +
                             ", Description='" + DBModule.RefineString(Description) + "'" +
                             ", Location='" + DBModule.RefineString(Location) + "'" +
                             " ,OrderNumber=" + OrderNumber.ToString() +
                    "  where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("GroupPermission", "edit", cn, trans);
                //GV.Response.Write(strSQL);
            }
            //throw new Exception(strSQL);
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_grouppermission WHERE GroupID = " + this.ID.ToString() + ";";
            strSQL += "DELETE FROM tbl_Groups WHERE ID = " + this.ID.ToString() + ";";
            //GV.Response.Write("sqldelete=" + strSQL);
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("GroupPermission", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";


            strSQL = "SELECT * FROM tbl_Groups WHERE ID = " + ID.ToString() + " limit 0,1";


            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Name")) Name = dr["Name"].ToString();
                if (!dr.IsNull("Code")) Code = dr["Code"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("Location")) Location = dr["Location"].ToString();
                if (!dr.IsNull("OrderNumber")) OrderNumber = long.Parse(dr["OrderNumber"].ToString());
            }
        }
        public void GetList(string Condittion, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {

            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_Groups  where 1=1 ";
            if (!string.IsNullOrEmpty(Condittion))
            {
                strSQL += Condittion;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //GV.Response.Write(strSQL);
        }

        public static int GetObjectPermissionByGroup(string groupID, string objectID, MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "";
            DataSet ds = null;
            // build SQL statement
            strSQL = "Select PermissionValue from tbl_grouppermission  where 1=1 ";
            strSQL += " AND GroupID=" + groupID + " AND ObjectID=" + objectID;
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                return int.Parse(ds.Tables[0].Rows[0]["PermissionValue"].ToString());
            else
                return 0;

        }
        public static bool IsUserInGroup(string groupID, string userID, MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "";
            DataSet ds = null;
            // build SQL statement
            strSQL = "Select ID from tbl_groupuser  where 1=1 ";
            strSQL += " AND GroupID=" + groupID + " AND UserID=" + userID;
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                return true;
            else
                return false;

        }

        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_Groups where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public bool CheckExist(string PemissionCode, long ID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Select count(ID) from tbl_Groups WHERE Code='" + PemissionCode + "'";
            if (ID > 0)
                strSQL += " AND ID <>" + ID.ToString();
            // run SQL statement
            int ncount = int.Parse(DBModule.ExecuteQueryForOneResult(strSQL, cn, trans));
            if (ncount > 0)
                return true;
            else
                return false;
        }
        public static void AssignPermissionToGroup(string groupID, string objectID, string permissionValue, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_grouppermission";
            long ID = 0;
            //check Exist
            strSQL = "Select ID From tbl_grouppermission where groupID=" + groupID + " AND objectID=" + objectID;
            string strID = DBModule.ExecuteQueryForOneResult(strSQL, GV.cn, GV.trans);
            if (!string.IsNullOrEmpty(strID))
                ID = long.Parse(strID);
            if (ID <= 0) // new object, we insert new record to database
            {
                strSQL = "INSERT INTO " + strTableName + "(" +
                "GroupID," +
                "ObjectID," +
                "PermissionValue" +
                ")" +
                "VALUES(" +
                groupID + "," +
                objectID + "," +
                permissionValue +
                ");";
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //throw new Exception(strSQL);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                             " GroupID=" + groupID + "" +
                             ", ObjectID=" + objectID + "" +
                             ", PermissionValue=" + permissionValue + "" +
                    "  where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //GV.Response.Write(strSQL);
            }
            //throw new Exception(strSQL);
        }
        public static void AddUserToGroup(string groupID, string userID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_groupuser";
            long ID = 0;
            //check Exist
            strSQL = "Select ID From tbl_groupuser where groupID=" + groupID + " AND UserID=" + userID;
            string strID = DBModule.ExecuteQueryForOneResult(strSQL, GV.cn, GV.trans);
            if (!string.IsNullOrEmpty(strID))
                ID = long.Parse(strID);
            if (ID <= 0) // new object, we insert new record to database
            {
                strSQL = "INSERT INTO " + strTableName + "(" +
                "GroupID," +
                "UserID" +
                ")" +
                "VALUES(" +
                groupID + "," +
                userID +
                ");";
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //throw new Exception(strSQL);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                             " GroupID=" + groupID + "" +
                             ", UserID=" + userID + "" +
                    "  where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //GV.Response.Write(strSQL);
            }
            //throw new Exception(strSQL);
        }
        public static void RemoveUserToGroup(string groupID, string userID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            strSQL = "Delete From tbl_groupuser where GroupID=" + groupID + " AND UserID=" + userID;
            DBModule.ExecuteNonQuery(strSQL, GV.cn, GV.trans);
        }
        public static string GetAllUserLocation(string userID, MySqlConnection cn, MySqlTransaction trans)
        {
            string retVal = "-1";
            string strSQL = "";
            strSQL = "SELECT GROUP_CONCAT(location) FROM tbl_groups WHERE  ID IN (SELECT GroupID FROM tbl_groupuser WHERE userid=" + userID + ")";
            string lstLocation = DBModule.ExecuteQueryForOneResult(strSQL, GV.cn, GV.trans);
            if (!String.IsNullOrEmpty(lstLocation))
                retVal = lstLocation;
            return retVal;
        }
        public static string GetAllUserGroupCode(string userID, MySqlConnection cn, MySqlTransaction trans)
        {
            string retVal = "";
            string strSQL = "";
            strSQL = "SELECT GROUP_CONCAT(Code) FROM tbl_groups WHERE  ID IN (SELECT GroupID FROM tbl_groupuser WHERE userid=" + userID + ")";
            string lstLocation = DBModule.ExecuteQueryForOneResult(strSQL, GV.cn, GV.trans);
            if (!String.IsNullOrEmpty(lstLocation))
                retVal = lstLocation;
            return retVal;
        }
        public int GetUserGroupPermissions(string userID, string objectID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            DataSet ds = null;
            int reVal = 0;
            strSQL = "SELECT MAX(PermissionValue) as PermissionValue FROM tbl_grouppermission  " +
            " INNER JOIN tbl_groupuser ON tbl_grouppermission.GroupID=tbl_groupuser.GroupID " +
            " INNER JOIN tbl_groups ON tbl_grouppermission.GroupID=tbl_groups.id " +
            " WHERE tbl_grouppermission.ObjectID=" + objectID + " AND tbl_groupuser.UserID=" + userID;
            ds = DBModule.ExecuteQuery(strSQL, GV.cn, GV.trans);
            if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["PermissionValue"].ToString()))
            {
                reVal = int.Parse(ds.Tables[0].Rows[0]["PermissionValue"].ToString());
            }
            return reVal;
        }
    }
}

